﻿CREATE PROCEDURE [dbo].[televisors_listing]
    @page int,
    @size int,
    @column nvarchar(256),
    @direction int,
    @name nvarchar(256)=null
AS
    SELECT
        Televisors.TelevisorId,
        Televisors.Name,
        Televisors.Type,
        Televisors.Diagonal,
        Televisors.ThreeD,
        Televisors.ReleaseDate,
        Magazine.Name
    FROM Televisors,Magazine
        WHERE
        (@name IS NULL OR Televisors.Name LIKE '%'+@name+'%') and
        (Televisors.MagazineId = Magazine.MagazineId)
    ORDER BY
        CASE WHEN @column = 'Name' and @direction = 0 THEN Televisors.Name END ASC,
        CASE WHEN @column = 'Name' and @direction = 1 THEN Televisors.Name END DESC,
        CASE WHEN @column = 'Type' and @direction = 0 THEN Televisors.Type END ASC,
        CASE WHEN @column = 'Type' and @direction = 1 THEN Televisors.Type END DESC,
        CASE WHEN @column = 'Diagonal' and @direction = 0 THEN Televisors.Diagonal END ASC,
        CASE WHEN @column = 'Diagonal' and @direction = 1 THEN Televisors.Diagonal END DESC,
        CASE WHEN @column = 'TreeD' and @direction = 0 THEN Televisors.ThreeD END ASC,
        CASE WHEN @column = 'TreeD' and @direction = 1 THEN Televisors.ThreeD END DESC,
        CASE WHEN @column = 'ReleaseDate' and @direction = 0 THEN Televisors.ReleaseDate END ASC,
        CASE WHEN @column = 'ReleaseDate' and @direction = 1 THEN Televisors.ReleaseDate END DESC,
        CASE WHEN @column = 'Magazine' and @direction = 0 THEN Magazine.Name END ASC,
        CASE WHEN @column = 'Magazine' and @direction = 1 THEN Magazine.Name END DESC

    OFFSET ((@page-1)*@size) ROWS
    FETCH NEXT @size ROWS ONLY

    Declare @TotalCount int

    SELECT @TotalCount = Count(1)
    FROM Televisors
    WHERE 
    (@name IS NULL OR Televisors.Name LIKE '%'+ @name + '%')
            
RETURN @TotalCount;
